package com.tyqx.detectcputemperaturesendmessage.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.*;

/**
 * @ClassName ExportExcel
 * @Description List集合导出成Excel表格工具类
 * @Author Tony.Tong
 * @Date 2018/9/25 14:03
 **/
public final class ExportExcel {
    /**
     * 工作簿
     */
    private static HSSFWorkbook workbook;
    /**
     * sheet
     */
    private static HSSFSheet sheet;
    /**
     * 标题行开始的位置
     */
    private static final int TITLE_START_POSITION = 0;
    /**
     * 时间行开始的位置
     */
    private static final int DATEHEAD_START_POSITION = 1;
    /**
     * 表头行开始的位置
     */
    private static final int HEAD_START_POSITION = 2;
    /**
     * 文本行开始的位置
     */
    private static final int CONTENT_START_POSITION = 3;


    public static byte[] excelExport(List<LinkedHashMap<String,Object>> dataList, LinkedHashMap<String, String>
            titleMap, String sheetName, String timeRange, HttpServletRequest request, HttpServletResponse response) throws IOException{
        //初始化workbook
        initHSSFWorkbook(sheetName);
        //填充标题行
        createTitleRow(titleMap, sheetName);
        //填充时间行
        createDateHeadRow(titleMap,timeRange);
        //填充表头行
        creatHeadRow(titleMap);
        //填充文本内容
        createContentRow(dataList, titleMap);
        //设置列自动伸缩
        autoSizeColumn(titleMap.size());
        //写入结果
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        }
//        HttpHeaders headers = new HttpHeaders();
//        //如果是IE浏览器，则用URLEncode解析
//        if(isMSBrowser(request)){
//            sheetName = URLEncoder.encode(sheetName, "UTF-8");
//        }else{//如果是谷歌、火狐则解析为ISO-8859-1
//            sheetName = new String(sheetName.getBytes("UTF-8"), "ISO-8859-1");
//        }
//        //通知浏览器以attachment（下载方式)
//        headers.setContentDispositionFormData("attachment", sheetName+".xls");
//        //application/octet-stream二进制流数据（最常见的文件下载）。
//        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
//        ResponseEntity<byte[]> filebyte = new ResponseEntity<byte[]>(out.toByteArray(),headers, HttpStatus.CREATED);
//        return filebyte;
        return out.toByteArray();
    }
    /**
     * @Title initHSSFWorkbook
     * @Description 创建sheet
     * @Date 2018/9/25
     * @Param [sheetName]
     * @Return void
     * @Author Tony.Tong
     */
    private static void initHSSFWorkbook(String sheetName) {
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet(sheetName);
    }

    /**
     * @Title createTitleRow
     * @Description 生成标题（第零行创建）
     * @Date 2018/9/25
     * @Param [titleMap, sheetName]表头名称，sheet名称
     * @Return void
     * @Author Tony.Tong
     */
    private static void createTitleRow(Map<String, String> titleMap, String sheetName) {
        CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, titleMap.size() - 1);
        sheet.addMergedRegion(titleRange);
        HSSFRow titleRow = sheet.createRow(TITLE_START_POSITION);
        HSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(sheetName);
    }

    /**
     * @Title createDateHeadRow
     * @Description 创建时间行 （第一行创建）
     * @Date 2018/9/25
     * @Param [titleMap]表头名称
     * @Return void
     * @Author Tony.Tong
     */
    private static void createDateHeadRow(Map<String, String> titleMap,String timeRange) {
        CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, titleMap.size() - 1);
        sheet.addMergedRegion(dateRange);
        HSSFRow dateRow = sheet.createRow(DATEHEAD_START_POSITION);
        HSSFCell dateCell = dateRow.createCell(0);
        dateCell.setCellValue(timeRange);
    }

    /**
     * @Title creatHeadRow
     * @Description 创建表头行（第二行创建）
     * @Date 2018/9/25
     * @Param [titleMap]表头名称
     * @Return void
     * @Author Tony.Tong
     */
    private static void creatHeadRow(LinkedHashMap<String, String> titleMap) {
        //第一行创建
        HSSFRow headRow = sheet.createRow(HEAD_START_POSITION);
        int i = 0;
        for (String entry : titleMap.keySet()
        ) {
            HSSFCell headCell = headRow.createCell(i);
            headCell.setCellValue(titleMap.get(entry));
            i++;
        }
    }

    /**
     * @Title createContentRow
     * @Description 文本创建
     * @Date 2018/9/25
     * @Param [dataList, titleMap]对象数据集合，表头信息
     * @Return void
     * @Author Tony.Tong
     */
    private static void createContentRow(List<?> dataList, Map<String, String> titleMap) {
        try {
            for (int i = 0;i<dataList.size();i++){
                HSSFRow textRow = sheet.createRow(CONTENT_START_POSITION + i);
                Map<String,Object> map = (HashMap)dataList.get(i);
                for (int j = 0;j<titleMap.size();){
                    for (HashMap.Entry<String, Object> entry : map.entrySet()){
                        Object value = entry.getValue();
                        HSSFCell textCell = textRow.createCell(j);

                        if (value instanceof Integer) {
                            int valueInt = ((Integer) value).intValue();
                            textCell.setCellValue(valueInt);
                        } else if (value instanceof String) {
                            String valueStr = (String) value;
                            textCell.setCellValue(valueStr);
                        } else if (value instanceof Double) {
                            double valueDouble = ((Double) value).doubleValue();
                            textCell.setCellValue(valueDouble);
                        } else if (value instanceof Float) {
                            float valueFloat = ((Float) value).floatValue();
                            textCell.setCellValue(valueFloat);
                        } else if (value instanceof Long) {
                            long valueLong = ((Long) value).longValue();
                            textCell.setCellValue(valueLong);
                        } else if (value instanceof Boolean) {
                            boolean valueBoolean = ((Boolean) value).booleanValue();
                            textCell.setCellValue(valueBoolean);
                        } else if (value instanceof Date) {
                            Date valueDate = (Date) value;
                            textCell.setCellValue(valueDate);
                        }
                        j++;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @Title autoSizeColumn
     * @Description 自动伸缩列 （如非必要，请勿打开此方法，耗内存）
     * @Date 2018/9/25
     * @Param [size]
     * @Return void
     * @Author Tony.Tong
     */
    private static void autoSizeColumn(Integer size) {
        for (int j = 0; j < size; j++) {
            sheet.autoSizeColumn(j);
        }
    }
    /**
     * 方法功能描述: 判断是否是IE浏览器
     * @param request
     * @return
     */
    public static boolean isMSBrowser(HttpServletRequest request) {
        String[] IEBrowserSignals = {"MSIE", "Trident", "Edge"};
        String userAgent = request.getHeader("User-Agent");
        for (String signal : IEBrowserSignals) {
            if (userAgent.contains(signal)){
                return true;
            }
        }
        return false;
    }
}
